/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bl;

import common.Common;
import dal.ConnectDatabase;
import dal.QueryExecuter;
import entities.Attendance;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;

/**
 *
 * @author Sony
 */
public class AttendanceBO {

    private QueryExecuter query = new QueryExecuter();
    Connection connect = ConnectDatabase.getConnection();
    Common common = new Common();

    public Vector<Attendance> showAttendanceCurrentDay() {
        Vector<Attendance> data = new Vector<Attendance>();
        DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
        Calendar calendar = Calendar.getInstance();
        String today = df.format(calendar.getTime());
        String sql = "SELECT * FROM Attendance where Day = '" + today + "'";
        try {
            Statement stm = connect.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                Attendance attendance = new Attendance();
                attendance.setLaborID(rs.getNString(1));
                attendance.setDay(common.converDateSqlToUtil(rs.getDate(2)));
                attendance.setDailyAttendance(rs.getBoolean(3));
                attendance.setShiftID(rs.getNString(4));
                attendance.setExtraShift(rs.getInt(5));
                attendance.setSalaryDaily(rs.getFloat(6));

                data.add(attendance);
            }
            rs.close();
            stm.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return data;
    }

    public Vector<Attendance> showAllAttendance() {
        Vector<Attendance> data = new Vector<Attendance>();
        String sql = "SELECT * FROM Attendance";
        try {
            Statement stm = connect.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                Attendance attendance = new Attendance();
                attendance.setLaborID(rs.getNString(1));
                attendance.setDay(common.converDateSqlToUtil(rs.getDate(2)));
                attendance.setDailyAttendance(rs.getBoolean(3));
                attendance.setShiftID(rs.getNString(4));
                attendance.setExtraShift(rs.getInt(5));
                attendance.setSalaryDaily(rs.getFloat(6));

                data.add(attendance);
            }
            rs.close();
            stm.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return data;
    }

    public Vector<Attendance> showAttendanceByLabor(String LaborID) {
        Vector<Attendance> data = new Vector<Attendance>();
        String sql = "SELECT * FROM [Attendance] where [LaborID] = '" + LaborID + "'";
        try {
            Statement stm = connect.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                Attendance attendance = new Attendance();
                attendance.setLaborID(rs.getNString(1));
                attendance.setDay(common.converDateSqlToUtil(rs.getDate(2)));
                attendance.setDailyAttendance(rs.getBoolean(3));
                attendance.setShiftID(rs.getNString(4));
                attendance.setExtraShift(rs.getInt(5));
                attendance.setSalaryDaily(rs.getFloat(6));

                data.add(attendance);
            }
            rs.close();
            stm.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return data;
    }

    public void addComboBox(JComboBox comboBox) {
        try {
            String sql = "SELECT shiftID from Shift";
            ResultSet rs = query.exeQuerry(sql);
            while (rs.next()) {
                String str = rs.getString("shiftID");
                comboBox.addItem(str);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LaborBO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public float getSalaryPerHour(String s) {
        float salaryPerHour = 0;
        try {
            String sql = "select SalaryPerHour from Salary where SalaryGrade =" + "'" + s + "'";
            ResultSet rs = query.exeQuerry(sql);
            while (rs.next()) {
                salaryPerHour = Float.parseFloat(rs.getString("SalaryPerHour"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(AttendanceBO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return salaryPerHour;
    }

    public float getSalaryPerHourByID(String LaborID) {
        float salaryPerHour = 0;
        try {
            String sql = "SELECT SalaryPerHour\n"
                    + "FROM Labor INNER JOIN Salary ON Labor.SalaryGrade = Salary.SalaryGrade\n"
                    + "WHERE LaborID = ?";
            PreparedStatement pstm = connect.prepareStatement(sql);
            pstm.setNString(1, LaborID);
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                salaryPerHour = rs.getFloat(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return salaryPerHour;
    }

    public float calculateSalaryDaily(int extrashift, float salaryPerHour, int timeWorking) {
        return ((timeWorking + extrashift) * salaryPerHour);
    }

    public int addAttendance(Attendance attendance) {
        java.sql.Date sqlDate = common.convertDateUtilToSql(attendance.getDay());
        String insert = "insert into Attendance(Day,LaborID,DailyAttendance,ShiftID,ExtraShift,SalaryDaily)"
                + " values('" + sqlDate + "','" + attendance.getLaborID() + "',"
                + (new Common()).checkBoolean(attendance.isDailyAttendance()) + ",'" + attendance.getShiftID()
                + "'," + attendance.getExtraShift() + "," + attendance.getSalaryDaily() + ")";
        int rs = query.updateQuerry(insert);
        return rs;
    }

    public String getTotalExtraShift(java.sql.Date dateFrom, java.sql.Date dateTo, String LaborID) {
        String totalExtraShift = "";
        String sql = "select SUM ([ExtraShift])as [Total Extra Shift] from Attendance where [Day] "
                + "between ? and ? and LaborID = ?";
        try {
            PreparedStatement pstm = connect.prepareStatement(sql);
            pstm.setDate(1, dateFrom);
            pstm.setDate(2, dateTo);
            pstm.setString(3, LaborID);
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                totalExtraShift = String.valueOf(rs.getInt(1));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return totalExtraShift;
    }

    public String getTotalAttendance(java.sql.Date dateFrom, java.sql.Date dateTo, String LaborID) {
        String totalAttendance = "";
        String sql = "select COUNT ([DailyAttendance]) as [Total Attendance] from Attendance where [Day] \n"
                + "                 between ? and ? and [DailyAttendance] = 1 and LaborID = ?";
        try {
            PreparedStatement pstm = connect.prepareStatement(sql);
            pstm.setDate(1, dateFrom);
            pstm.setDate(2, dateTo);
            pstm.setString(3, LaborID);
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                totalAttendance = String.valueOf(rs.getInt(1));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return totalAttendance;
    }

    public String getTotalSalary(java.sql.Date dateFrom, java.sql.Date dateTo, String LaborID) {
        String totalSalary = "";
        String sql = "select SUM ([SalaryDaily])as [Total] from [Attendance] where [Day] "
                + "between ? and ? and [LaborID] = ?";
        try {
            PreparedStatement pstm = connect.prepareStatement(sql);
            pstm.setDate(1, dateFrom);
            pstm.setDate(2, dateTo);
            pstm.setString(3, LaborID);
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                totalSalary = String.valueOf(rs.getInt(1));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return totalSalary;
    }

    public boolean editAttendance(Attendance attendance) {
        int dailyAttendance = common.checkBoolean(attendance.isDailyAttendance());
        if (attendance.isDailyAttendance() == true) {
            String update = "update [Attendance] set [DailyAttendance]= " + dailyAttendance + ""
                    + ",[ShiftID]='" + attendance.getShiftID() + "',[ExtraShift]= " + attendance.getExtraShift() + ",[SalaryDaily] = " + attendance.getSalaryDaily() + "\n"
                    + "where LaborID = '" + attendance.getLaborID() + "' and [Day] = '" + attendance.getDay() + "'";
            int rt = query.updateQuerry(update);
            return rt > 0 ? true : false;
        } else {
            String update = "update [Attendance] set [DailyAttendance]= " + dailyAttendance + ""
                    + ",[ShiftID]='" + attendance.getShiftID() + "',[ExtraShift]= " + attendance.getExtraShift() + ",[SalaryDaily] = " + 0 + "\n"
                    + "where LaborID = '" + attendance.getLaborID() + "' and [Day] = '" + attendance.getDay() + "'";
            int rt = query.updateQuerry(update);
            return rt > 0 ? true : false;
        }
    }
}
